/********************************************************************************
 * Project Name		[JavaEE_JDBC]
 * File Name     	[TestBatch.java]
 * Creation Date 	[2015-01-01]
 * 
 * Copyright© ge.y.yang@gmail.com All Rights Reserved
 * 
 * Work hard, play harder, think big and keep fit
 ********************************************************************************/
package jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import utils.jdbc.JdbcUtils;

/**
 * 测试批处理<br/>
 * 
 * 方式1: 使用java.sql.Statement<br/>
 * <code>
 * Connection connection = JdbcUtils.getConnection();
 * String sql1 = "INSERT INTO user(id, name) VALUES(1, 'user1')";
 * String sql3 = "INSERT INTO user(id, name) VALUES(2, 'user2')";
 * String sql3 = "INSERT INTO user(id, name) VALUES(3, 'user3')";
 * 
 * Statement statement = connection.createStatement();
 * statement.addBatch(sql1);
 * statement.addBatch(sql2);
 * statement.addBatch(sql3);
 * 
 * statement.executeBatch();
 * statement.clearBatch();
 * </code>
 * 
 * 使用Statement的优缺点:<br/>
 * 优点: 可以向数据库发送多条不同的SQL语句<br/>
 * 缺点: SQL语句没有预编译, 当向数据库发送多条语句相同, 仅参数不同的SQL语句时, 需重复写很多条SQL语句<br/>
 * 
 * 方式2: 使用java.sql.PreparedStatement<br/>
 * <code>
 * // 见下例
 * </code>
 * 
 * 使用PreparedStatement的优缺点:<br/>
 * 优点: 发送的是预编译后的SQL语句, 执行效率高<br/>
 * 缺点: 只能应用在SQL语句相同, 但参数不同的批处理中, 此种形式的批处理经常用于在同一个表中批量插入或批量更新表的数据<br/>
 * 
 * @author 不落的太阳(Sean Yang aka ShortPeace)
 * @version 1.0
 * @since jdk 1.8
 * 
 */
public class TestBatch {

	private Connection connection;
	private PreparedStatement statement;
	private ResultSet resultSet;

	@Before
	public void init() throws Exception {
		connection = JdbcUtils.getInstance().getConnection();
	}

	/**
	 * 测试批处理保存所需时间
	 * 
	 * @throws Exception
	 */
	@Test
	public void testBatch() throws Exception {
		// 如果支持批处理
		if (connection.getMetaData().supportsBatchUpdates()) {
			String sql = "INSERT INTO User(username, password, age, gender, birthday) VALUES(?, ?, ?, ?, ?)";
			statement = connection.prepareStatement(sql);
			long begin = System.currentTimeMillis();

			for (int i = 10; i < 50000; i++) {
				statement.setString(1, "username" + i);
				statement.setString(2, "password" + i);
				statement.setInt(3, i);
				statement.setString(4, (i % 2 == 0 ? "F" : "M"));
				statement.setDate(5, new java.sql.Date(new java.util.Date().getTime()));
				// 添加到批处理
				statement.addBatch();

				// 每800条执行一次, 并清空先前积攒的SQL
				if ((i + 1) % 800 == 0) {
					statement.executeBatch();
					statement.clearBatch();
				}
			}

			// 如果总记录数不是批量值的整数倍, 则还需要额外执行一次
			if (50000 % 800 != 0) {
				statement.executeBatch();
				statement.clearBatch();
			}

			long end = System.currentTimeMillis();

			System.out.println("使用批处理: " + (end - begin));
		}
	}

	/**
	 * 测试正常保存所需时间
	 * 
	 * @throws Exception
	 */
	@Test
	public void testOneByOne() throws Exception {
		String sql = "INSERT INTO User(username, password, age, gender, birthday) VALUES(?, ?, ?, ?, ?)";
		statement = connection.prepareStatement(sql);
		long begin = System.currentTimeMillis();

		for (int i = 10; i < 50000; i++) {
			statement.setString(1, "username" + i);
			statement.setString(2, "password" + i);
			statement.setInt(3, i);
			statement.setString(4, (i % 2 == 0 ? "F" : "M"));
			statement.setDate(5, new java.sql.Date(new java.util.Date().getTime()));
			statement.executeUpdate();
		}

		long end = System.currentTimeMillis();
		System.out.println("未使用批处理: " + (end - begin));
	}

	@After
	public void destroy() {
		JdbcUtils.getInstance().close(resultSet, statement, connection);
	}
}
